﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace VentasTelcelMAC.Engine
{//http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734
    internal sealed class ExcelHandler
    {
        private DataSet _ExcelData;
        private string _ExcelFileName;

        public bool OpenExcelFile()
        {
            using (System.Windows.Forms.OpenFileDialog openFileDialog = new System.Windows.Forms.OpenFileDialog())
            {
                openFileDialog.Filter = "Archivos de Excel (.xls)|*.xlsx|All Files (*.*)|*.*";
                openFileDialog.FilterIndex = 1;

                openFileDialog.Multiselect = true;

                if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    try
                    {
                        ReadExcelFile(openFileDialog.FileName);
                        _ExcelFileName = openFileDialog.FileName;
                    }catch
                    {
                        return false;
                        //throw new Exception("El Archivo no pudo leerse");
                    }
                }
                return true;
            }
        
        }
      
        private void ReadExcelFile(string ExcelFilePath)
        {
            string strOledbConnection;

            strOledbConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath +
                             ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";

            OleDbConnection conn = new OleDbConnection(strOledbConnection);
            conn.Open();
            DataTable dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string strsheetName = "Sheet1$";
            if (dbSchema != null && dbSchema.Rows.Count > 0)
            {
                strsheetName = Convert.ToString(dbSchema.Rows[0]["TABLE_NAME"]);
            }
            conn.Close();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + strsheetName + "] ", strOledbConnection);
            _ExcelData = new DataSet();
            myCommand.Fill(_ExcelData);

        }

        public string ExcelFileName
        {
            get
            {
                if (_ExcelFileName != string.Empty)
                    return _ExcelFileName;
                else
                    throw new Exception("No hay Excel Cargado aun");
            }
        
        }
     
        public DataSet ExcelData
        {
            get
            {
                if (_ExcelData != null)
                    return _ExcelData;
                else
                    throw new Exception("No hay Informacion de Excel Cargada aun"); 
            }
            
            set
            {
                _ExcelData = value;
            }
    
        }
    }

}




